﻿using Sc.Domain;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sc.Persistence
{
    public class Sys_ToDoListDAO
    {
        #region  BasicMethod

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Sys_ToDoList model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Sys_ToDoList(");
            strSql.Append("UserId,Name,ActionURL,UrgencyDegree,State,CreateTime,UpdateTime,CreaterName)");
            strSql.Append(" values (");
            strSql.Append("@UserId,@Name,@ActionURL,@UrgencyDegree,@State,@CreateTime,@UpdateTime,@CreaterName)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Name", SqlDbType.NVarChar,150),
					new SqlParameter("@ActionURL", SqlDbType.NVarChar,100),
					new SqlParameter("@UrgencyDegree", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@UpdateTime", SqlDbType.DateTime),
					new SqlParameter("@CreaterName", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.UserId;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.ActionURL;
            parameters[3].Value = model.UrgencyDegree;
            parameters[4].Value = model.State;
            parameters[5].Value = model.CreateTime;
            parameters[6].Value = model.UpdateTime;
            parameters[7].Value = model.CreaterName;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Sys_ToDoList model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_ToDoList set ");
            strSql.Append("State=@State");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = model.State;
            parameters[1].Value = model.Id;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 根据业务ID更新待办事宜
        /// </summary>
        /// <param name="soruce">业务前缀+Id</param>
        /// <param name="state">状态</param>
        /// <returns></returns>
        public bool UpdateAll(string soruce,int state)
        {
            string sql = "UPDATE Sys_ToDoList SET State=@State WHERE Source=@Source";
            SqlParameter[] parameters = {
					new SqlParameter("@State", SqlDbType.Int),
                    new SqlParameter("@Source", SqlDbType.NVarChar)
			};
            parameters[0].Value = state;
            parameters[1].Value = soruce;// "UCAC" + bid;
            return DbHelperSQL.ExecuteSql(sql, parameters) > 0 ? true : false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Sys_ToDoList ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Sys_ToDoList ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion  BasicMethod
        #region UserMethod
        /// <summary>
        /// 保存多条待办事宜
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int SaveToDoList(IList<Sys_ToDoList> list)
        {
            List<string> sqls = new List<string>();
            foreach (var item in list)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into Sys_ToDoList(");
                strSql.Append("UserId,Name,ActionURL,UrgencyDegree,State,CreaterName,Source)");
                strSql.Append(" values (");
                strSql.Append(item.UserId).Append(",")
                    .Append("'").Append(item.Name).Append("',")
                    .Append("'").Append(item.ActionURL).Append("',")
                    .Append(item.UrgencyDegree).Append(",")
                    .Append(item.State).Append(",")
                    .Append("'").Append(item.CreaterName).Append("',")
                    .Append("'").Append(item.Source).Append("')");
                sqls.Add(strSql.ToString());
            }
            return DbHelperSQL.ExecuteSqlTran(sqls);
        }

        /// <summary>
        /// 获取详情（单个实体）
        /// </summary>
        /// <param name="Id">Id</param>
        /// <returns></returns>
        public Sys_ToDoList GetDetail(int Id)
        {
            Sys_ToDoList result = null;
            string sql = "SELECT * FROM Sys_ToDoList WHERE Id=@Id";

            SqlParameter parm = new SqlParameter("@Id", SqlDbType.Int);
            parm.Value = Id;

            using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parm))
            {
                if (sdr.Read())
                {
                    result = new Sys_ToDoList();
                    result = BuildObjectFromIDataReader(sdr);
                }
                sdr.Close();
            }
            return result;
        }
        /// <summary>
        /// 从IDataReader中读取数据，组装成实体
        /// </summary>
        /// <param name="dr">数据读取接口</param>
        /// <returns>Sys_ToDoList实体</returns>
        /// <remarks>
        /// </remarks>
        private Sys_ToDoList BuildObjectFromIDataReader(IDataReader dr)
        {
            try
            {
                Sys_ToDoList obj = new Sys_ToDoList();
                obj.Id = (dr["Id"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Id")));
                obj.UserId = (dr["UserId"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("UserId")));
                obj.Name = (dr["Name"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Name")));
                obj.ActionURL = (dr["ActionURL"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("ActionURL")));
                obj.UrgencyDegree = (dr["UrgencyDegree"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("UrgencyDegree")));
                obj.State = (dr["State"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("State")));
                obj.CreateTime = (dr["CreateTime"] == System.DBNull.Value ? new DateTime(1900, 1, 1) : dr.GetDateTime(dr.GetOrdinal("CreateTime")));
                obj.UpdateTime = (dr["UpdateTime"] == System.DBNull.Value ? new DateTime(1900, 1, 1) : dr.GetDateTime(dr.GetOrdinal("UpdateTime")));
                obj.CreaterName = (dr["CreaterName"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("CreaterName")));
                obj.Source = (dr["Source"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Source")));

                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception("从DataReader中读取数据失败，可能与相关的字段不存在有关！请检查！||" + ex.Message + "||" + ex.Source);
            }
        }
        /// <summary>
        /// 获取当前用户未读消息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns></returns>
        public int GetNoReadMsgCountByUserId(int userId)
        {
            string sql = "SELECT COUNT(*) FROM Sys_ToDoList WHERE UserId=@UserId AND State=0";
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@UserId",SqlDbType.Int)
            };
            param[0].Value = userId;
            return Convert.ToInt32(DbHelperSQL.GetSingle(sql, param));
        }
        /// <summary>
        /// 优化性能，返回sql执行模型
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public HelperModel AddToDo(Sys_ToDoList model)
        {
            HelperModel ent = new HelperModel();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Sys_ToDoList(");
            strSql.Append("UserId,Name,ActionURL,UrgencyDegree,State,CreaterName,Source)");
            strSql.Append(" values (");
            strSql.Append("@UserId,@Name,@ActionURL,@UrgencyDegree,@State,@CreaterName,@Source)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Name", SqlDbType.NVarChar,150),
					new SqlParameter("@ActionURL", SqlDbType.NVarChar,100),
					new SqlParameter("@UrgencyDegree", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@CreaterName", SqlDbType.NVarChar,50),
					new SqlParameter("@Source", SqlDbType.NVarChar,20)};
            parameters[0].Value = model.UserId;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.ActionURL;
            parameters[3].Value = model.UrgencyDegree;
            parameters[4].Value = model.State;
            parameters[5].Value = model.CreaterName;
            parameters[6].Value = model.Source;
            ent.Sql = strSql.ToString(); ent.Parameter = parameters;
            return ent;
        }
        #endregion
    }
}
